﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace KC.DataAccess.Global
{
	/// <summary>Global methods for SQL access</summary>
    public static class SQL
    {
		/// <summary>Blindly executes a SQL query.</summary>
		/// <param name="conn">SqlConnection object</param>
		/// <param name="Query">Query to execute</param>
		public static void ExecuteNonQuery(ref SqlConnection conn, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			SqlCommand cmd = PrepSqlConnection(ref conn, Query);
			ExecuteNonQueryInner(ref cmd);
		}
		/// <summary>Blindly executes a SQL query.</summary>
		/// <param name="ConnStr">SQL Connection String</param>
		/// <param name="Query">Query to execute</param>
		public async static Task ExecuteNonQuery(string ConnStr, string Query)
		{
			if (string.IsNullOrEmpty(ConnStr)) throw new ArgumentNullException("ConnStr");
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			SqlConnection conn = new SqlConnection(ConnStr);
			SqlCommand cmd = PrepSqlConnection(ref conn, Query);
			Exception exc = null;
			for (int i = 0; i < 3; i++)
				try { await Task.Run(() => cmd.ExecuteNonQuery()); exc = null; break; }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			if (exc != null) throw new ApplicationException("Command failed after maximum attempts", exc);
			conn.Close();
			conn.Dispose();
		}
		/// <summary>Blindly executes a SQL query.</summary>
		/// <param name="ConnStr">SQL Connection String</param>
		/// <param name="Query">Query to execute</param>
		/// <param name="Parms">Array of SQL Parameters to pass into SQL</param>
		public async static Task ExecuteNonQuery(string ConnStr, string Query, SqlParameter[] Parms)
		{
			if (string.IsNullOrEmpty(ConnStr)) throw new ArgumentNullException("ConnStr");
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			SqlConnection conn = new SqlConnection(ConnStr);
			SqlCommand cmd = PrepSqlConnection(ref conn, Query);
			if (Parms != null)
				Parallel.ForEach(Parms, p => cmd.Parameters.Add(p));
			await Task.Run(new Action(() => ExecuteNonQueryInner(ref cmd)));
			cmd.Dispose();
			conn.Close();
			conn.Dispose();
		}

		/// <summary>Executes a stored procedure.</summary>
		/// <param name="conn">SqlConnection object</param>
		/// <param name="sproc">Stored Procedure to execute</param>
		/// <param name="Parms">Array of SQL Parameters</param>
		public static void ExecuteStoredProc(ref SqlConnection conn, string sproc, SqlParameter[] Parms = null)
		{
			if (conn == null) throw new ArgumentNullException("conn");
			if (string.IsNullOrEmpty(sproc)) throw new ArgumentNullException("sproc");
			SqlCommand cmd = PrepSqlConnection(ref conn, sproc);
			cmd.CommandType = CommandType.StoredProcedure;
			if (Parms != null)
				Parallel.ForEach(Parms, p => cmd.Parameters.Add(p));
			ExecuteNonQueryInner(ref cmd);
			cmd.Dispose();
		}
		/// <summary>Executes a stored procedure.</summary>
		/// <param name="ConnStr">SQL Connection String</param>
		/// <param name="sproc">Stored Procedure to execute</param>
		/// <param name="Parms">Array of SQL Parameters</param>
		public async static Task ExecuteStoredProc(string ConnStr, string sproc, SqlParameter[] Parms = null)
		{
			if (string.IsNullOrEmpty(ConnStr)) throw new ArgumentNullException("ConnStr");
			if (string.IsNullOrEmpty(sproc)) throw new ArgumentNullException("sproc");
			SqlConnection conn = new SqlConnection(ConnStr);
			SqlCommand cmd = PrepSqlConnection(ref conn, sproc);
			cmd.CommandType = CommandType.StoredProcedure;
			if (Parms != null)
				Parallel.ForEach(Parms, p => cmd.Parameters.Add(p));
			await Task.Run(new Action(() => ExecuteNonQueryInner(ref cmd)));
			cmd.Dispose();
			conn.Dispose();
		}

		/// <summary>Converts SqlCommands into scalar objects.</summary>
		/// <param name="conn">SqlConnection object</param>
		/// <param name="Query">Query to execute</param>
		/// <returns>Object returned from SQL Server</returns>
		public static object ExecuteScalar(ref SqlConnection conn, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			SqlCommand cmd = PrepSqlConnection(ref conn, Query);
			object obj = ExecuteScalarInner(ref cmd);
			cmd.Dispose();
			return obj;
		}
		/// <summary>Converts SqlCommands into scalar objects.</summary>
		/// <param name="ConnStr">SQL Connection String</param>
		/// <param name="Query">Query to execute</param>
		/// <returns>Object returned from SQL Server</returns>
		public async static Task<object> ExecuteScalar(string ConnStr, string Query)
		{
			if (string.IsNullOrEmpty(ConnStr)) throw new ArgumentNullException("ConnStr");
			SqlConnection conn = new SqlConnection(ConnStr);
			SqlCommand cmd = PrepSqlConnection(ref conn, Query);
			Exception exc = null;
			Object obj = null;
			for (int i = 0; i < 3; i++)
				try { await Task.Run(() => obj = cmd.ExecuteScalar()); exc = null; break; }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			if (exc != null) throw new ApplicationException("Command failed after maximum attempts", exc);
			cmd.Dispose();
			return obj;
		}

		/// <summary>Converts SQL queries into datatables</summary>
		/// <param name="conn">SQL connection object</param>
		/// <param name="Query">SQL Query to execute</param>
		/// <returns>DataTable containing results from SQL Server</returns>
		public static DataTable ExecuteDataTable(ref SqlConnection conn, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			return ExecuteDatatableInner(ref conn, Query);
		}
		/// <summary>Converts SQL queries into datatables</summary>
		/// <param name="ConnStr">SQL Connection String</param>
		/// <param name="Query">SQL Query to execute</param>
		/// <returns>DataTable containing results from SQL Server</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
		public async static Task<DataTable> ExecuteDataTable(string ConnStr, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			if (string.IsNullOrEmpty(ConnStr)) throw new ArgumentNullException("ConnStr");
			SqlConnection conn = new SqlConnection(ConnStr);
			SqlDataAdapter da = new SqlDataAdapter(Query, conn);
			da.SelectCommand.CommandTimeout = 60;

			Exception exc = null;
			DataTable result = new DataTable();
			for (int i = 0; i < 3; i++)
				try {
					await Task.Run(() => da.Fill(result));
					return result; }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			throw new ApplicationException("Command failed after maximum attempts", exc);
		}

		#region Helper Methods
		/// <summary>Converts connection objects and queries into SqlCommands ready for execution</summary>
		/// <param name="conn">Initialized SqlConnection object</param>
		/// <param name="Query">SQL Query</param>
		/// <returns>SqlCommand ready for execution</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
		private static SqlCommand PrepSqlConnection(ref SqlConnection conn, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			if (conn.State == ConnectionState.Closed) conn.Open();
			else if (conn.State == ConnectionState.Broken) {
				conn.Close();
				conn.Open(); }
			return new SqlCommand(Query, conn) { CommandTimeout = 60 };
		}
		/// <summary>Blindly executes a SqlCommand.</summary>
		/// <param name="command">SqlCommand to execute</param>
		private static void ExecuteNonQueryInner(ref SqlCommand command)
		{
			Exception exc = null;
			for (int i = 0; i < 3; i++)
				try { command.ExecuteNonQuery(); return; }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			throw new ApplicationException("Command failed after maximum attempts", exc);
		}
		/// <summary>Converts SqlCommands into scalar objects.</summary>
		/// <param name="command">SqlCommand to execute</param>
		/// <returns>Object returned from SQL Server</returns>
		private static object ExecuteScalarInner(ref SqlCommand command)
		{
			Exception exc = null;
			for (int i = 0; i < 3; i++)
				try { return command.ExecuteScalar(); }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			throw new ApplicationException("Command failed after maximum attempts", exc);
		}
		/// <summary>Converts SQL queries into datatables</summary>
		/// <param name="conn">Prepared SQL Connection</param>
		/// <param name="Query">SQL Query to execute</param>
		/// <returns>DataTable containing results from SQL Server</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
		private static DataTable ExecuteDatatableInner(ref SqlConnection conn, string Query)
		{
			if (string.IsNullOrEmpty(Query)) throw new ArgumentNullException("Query");
			SqlDataAdapter da = new SqlDataAdapter(Query, conn);
			da.SelectCommand.CommandTimeout = 60;

			Exception exc = null;
			DataTable result = new DataTable();
			for (int i = 0; i < 3; i++)
				try {
					da.Fill(result);
					return result; }
				catch (Exception ex) { Thread.Sleep(50); exc = ex; }
			throw new ApplicationException("Command failed after maximum attempts", exc);
		}
		#endregion Helper Methods
	}
}